// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.

suite("test_join_result_count", "query,p0") {

    sql "drop table if exists t3;"

    sql """
    CREATE TABLE `t3` (
`c0` datev2 NOT NULL ,
`c1` varchar(50) NOT NULL ,
`c2` varchar(50) NOT NULL ,
`c3` varchar(20) NOT NULL ,
`c4` varchar(50) NOT NULL ,
`c5` varchar(20) NOT NULL ,
`c6` varchar(100) NOT NULL ,
`c7` decimalv3(38, 18) NOT NULL ,
`c8` decimalv3(38, 18) NOT NULL ,
`c9` decimalv3(38, 18) NOT NULL ,
`c10` decimalv3(38, 18) NOT NULL ,
`c11` decimalv3(38, 18) NOT NULL ,
`c12` decimalv3(38, 9) NOT NULL ,
`c13` decimalv3(38, 18) NOT NULL ,
`c14` decimalv3(10, 0) NOT NULL ,
`c15` decimalv3(38, 18) NOT NULL ,
`c16` decimalv3(38, 18) NOT NULL ,
`c17` decimalv3(38, 18) NOT NULL ,
`c18` varchar(200) NOT NULL ,
`c19` varchar(60) NOT NULL ,
`c20` varchar(60) NOT NULL ,
`c21` smallint(6) NOT NULL ,
`c22` char(2) NOT NULL ,
`c23` datev2 NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(`c0`, `c1`, `c2`, `c3`)
PARTITION BY RANGE(`c0`)
(
PARTITION p_2000 VALUES [('2000-01-01'), ('2099-01-01'))
)
DISTRIBUTED BY HASH(`c0`, `c1`, `c2`, `c3`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false"
);
"""

    sql "drop table if exists t4;"

    sql """
CREATE TABLE `t4`
(
`d0` varchar(50) NULL ,
`d1` varchar(80) NULL ,
`d2` varchar(40) NULL ,
`d3` varchar(30) NULL ,
`d4` varchar(50) NULL ,
`d5` varchar(8) NULL ,
`d6` varchar(100) NULL ,
`d7` varchar(40) NULL ,
`d8` varchar(200) NULL ,
`d9` varchar(3) NULL ,
`d10` varchar(10) NULL ,
`d11` varchar(20) NULL ,
`d12` varchar(10) NULL ,
`d13` varchar(20) NULL ,
`d14` varchar(200) NULL ,
`d15` varchar(100) NULL ,
`d16` varchar(100) NULL ,
`d17` varchar(50) NULL ,
`d18` datev2 NULL ,
`d19` datev2 NULL ,
`d20` varchar(2) NULL ,
`d21` varchar(20) NULL ,
`d22` varchar(50) NULL ,
`d23` varchar(50) NULL ,
`d24` varchar(50) NULL ,
`d25` varchar(50) NULL ,
`d26` varchar(50) NULL ,
`d27` varchar(50) NULL ,
`d28` varchar(50) NULL ,
`d29` varchar(50) NULL ,
`d30` varchar(8) NULL ,
`d31` varchar(100) NULL ,
`d32` datev2 NULL ,
`d33` datev2 NULL
) ENGINE=OLAP
DUPLICATE KEY(`d0`)
DISTRIBUTED BY HASH(`d0`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false"
);
"""

    sql """
INSERT INTO `t3` VALUES ('2017-01-01','73','1','xy','XYZ','zQ','0N',78.326461744024730726,78.148863384057232844,62.576725243619092236,40.441486336294099290,31.469270014735277558,28.782245553,33.829453793315923236,39,89.415314680352419216,48.277770337435450545,39.771029459737232017,'A3','OGK_345_DF3G','zZ',54,'Sc','2010-01-01'),
('2017-01-01','C7','1','qR','CDE','LV','hN',81.216425648502760142,28.925014306186767820,52.402422792994751075,5.812755708462516834,53.223879424390418910,13.433030608,1.481331564085870790,10,24.769926557473260971,88.539161343479310865,24.541794550608993058,'rW','MNH_34S_REKK','Gh',45,'Hu','2010-01-01'),
('2017-01-01','58','1','Yk','CDE','aH','XQ',78.392219414044635220,17.836054009716235976,75.521757437152712728,88.114569368422740929,62.297035646689863337,80.231758806,17.353573976555423210,18,79.688952848768112904,71.647546361197629529,44.315339148367524439,'n2','OGK_345_DF3G','hF',18,'Or','2010-01-01'),
('2017-01-01','Dg','1','tx','PLM','AZ','Uq',34.250098363937564556,79.587227354840741959,31.814047037176354965,59.917666377146970604,13.834441510087425615,0.365281261,85.497153321232268530,36,77.963919462338067371,32.149317431964937960,17.429028523835712229,'LT','MNH_34S_REKK','LS',15,'Vl','2010-01-01'),
('2018-01-01','If','1','np','CDE','wb','so',68.279310735621318674,36.879973446170546149,65.513020649488707476,49.847202243832249046,86.194752199665599617,50.454361838,43.347186613370912871,3,49.790111820361468719,74.289699744482439840,66.638144755346800134,'28','MNH_34S_REKK','73',96,'6C','2010-01-01'),
('2018-01-01','i3','1','jh','ABC','t6','XI',5.138878908729890884,84.930864324459359970,64.637955032208415597,54.938326563130267689,33.923818238453330567,26.320989671,12.294925863562896321,19,12.457533760912251474,61.360584268926811330,88.976385655941290403,'TE','AFD_GFFD_234DF','Ox',14,'fZ','2010-01-01'),
('2018-01-01','qe','1','kb','XYZ','IL','aT',84.433049408592361382,25.208840938251258951,7.515469155188074817,71.508519492161355211,4.440055388630681670,81.855700857,0.767694961897832592,53,70.124435774461415165,6.105987429865320820,0.993866026878107445,'wt','AFD_GFFD_234DF','MY',41,'mI','2010-01-01'),
('2017-01-01','cm','1','21','XYZ','H7','Df',12.177693662735298656,61.752358034653687543,79.822770795436730436,14.882751629037019505,70.928139172708888073,53.543966255,41.841997784000600470,35,6.767427047172696420,33.907540824720293700,23.183909440666088700,'bD','MNH_34S_REKK','ra',15,'af','2010-01-01'),
('2018-01-01','iR','1','C0','ABC','dw','Tk',55.633829470415250135,59.719272511914569753,11.808129171772744906,25.101202231834535087,9.607696585312472635,75.722867715,65.379149305517739900,36,36.564316122969582638,30.299285714344106376,46.966155570132298566,'oZ','MNH_34S_REKK','2n',81,'sk','2010-01-01'),
('2018-01-01','jT','1','wl','CDE','Wd','oy',70.617199985608908954,68.327012036528644761,23.199845522983460080,77.263245236932631416,13.412770480640487497,22.716667783,32.602714815263571998,77,53.321220219577682622,75.939119586784967887,34.188018831298959993,'Ce','AFD_GFFD_234DF','No',42,'Ki','2010-01-01'),
('2018-01-01','Pk','1','Lt','PLM','YZ','OZ',2.350255364296265998,11.625233757619484026,35.169240131797152394,5.277131874113814564,30.263333772552171727,20.564065177,67.735031914847684541,75,83.386604866065732868,80.167835905015604074,10.797712531398997108,'dX','MNH_34S_REKK','L8',11,'z5','2010-01-01'),
('2018-01-01','FT','1','Ej','CDE','1d','Ei',61.332057782663242036,31.402224136695730958,7.906350886877649168,89.782195394735600976,70.969055142406905184,44.520140148,76.315077292503175735,47,57.177942058642784670,4.166306008337098985,52.310617728176608418,'1D','OGK_345_DF3G','7M',32,'5T','2010-01-01'),
('2018-01-01','l7','1','Hw','XYZ','2w','yW',29.229702085581495287,42.869940413826561091,61.100646671045591915,21.242820597861463490,17.550717841445098757,3.116980760,10.426251340837983888,32,0.844693797947813335,33.770096134014698518,88.114100062344658926,'1F','OGK_345_DF3G','gJ',22,'Yv','2010-01-01'),
('2017-01-01','bw','1','9W','PLM','DE','Rn',6.665548240305814678,65.867289932367603820,87.515469708592898440,29.137475673560345400,23.581006750858248039,19.827642528,5.331209584153457481,86,74.978579652457219227,42.531435691822733006,66.316141574245043058,'WV','OGK_345_DF3G','ED',87,'hY','2010-01-01'),
('2017-01-01','lJ','1','zH','ABC','ZH','bB',71.643453760727270970,11.104257350875398143,27.181730008078343707,15.918035675856784986,41.190128258548230995,28.773219100,62.534434037399076284,79,51.370128418839532597,48.411691292347722825,24.353513847677076092,'1d','MNH_34S_REKK','FI',40,'Ka','2010-01-01'),
('2017-01-01','NT','1','jx','PLM','hX','C3',79.419242678302615841,1.751866939232817946,90.584881785853143175,17.383802609241839689,38.124354795655609389,39.959859245,64.824394171184935885,15,25.207455534423336448,83.738023834531470686,31.736267149099521231,'jP','AFD_GFFD_234DF','J9',64,'yd','2010-01-01'),
('2017-01-01','iP','1','ti','ABC','ma','Bc',25.475019320992927219,19.800921759804310891,46.728343199913874520,69.444029483172017716,17.449813589090254197,11.487363501,71.394627195137949046,73,44.499383143299315225,71.769214206702996328,25.758292436698892000,'8j','OGK_345_DF3G','V4',83,'Di','2010-01-01'),
('2018-01-01','4r','1','sy','XYZ','Z9','BL',81.469765872134634393,36.585506940599653559,76.780193929080580285,71.588580352270616740,54.363836157836653350,5.821007704,44.261248744523421346,23,39.678999169479683211,11.918334409614122400,38.409104983585979992,'yd','MNH_34S_REKK','NP',21,'B6','2010-01-01'),
('2018-01-01','1b','1','LB','PLM','tF','0L',22.968282723551851720,4.694502179661971135,13.604949793483747676,89.642556141327328669,80.332132691245881875,24.369953840,42.722950814067730171,53,68.136452671337581430,49.680663165585190430,43.994169430634118873,'2h','OGK_345_DF3G','dn',53,'tV','2010-01-01'),
('2018-01-01','DA','1','De','PLM','1q','vE',78.929890910221051612,57.448260906845065691,41.710661063478039380,46.680683333626305567,62.602229366062085466,44.209749163,2.904019766920477559,73,60.235917448111860609,70.862527329647133511,31.636736114626720026,'7U','AFD_GFFD_234DF','8p',12,'So','2010-01-01'),
('2017-01-01','YT','1','MP','ABC','oX','yr',49.956518890640005315,37.957476083415637945,88.263561377769121760,38.453515337978225849,79.825383407768070345,90.561833806,34.242907580080859872,31,18.809502615686008996,4.232083780384746761,7.936532248786515038,'nc','AFD_GFFD_234DF','oW',53,'18','2010-01-01'),
('2017-01-01','i3','1','67','CDE','kK','3l',17.600011795822952986,1.785512178350065206,7.329570249035338382,11.978802709459287245,17.519415048345563747,76.201670796,63.330001040764342975,32,56.455032520773707978,69.500928788023164024,46.453886654269917419,'Pa','AFD_GFFD_234DF','Je',80,'xd','2010-01-01'),
('2018-01-01','hT','1','np','ABC','7j','uD',87.782639454391268394,64.784308112165164957,64.768052876040344764,48.476538057833478220,68.275842269683556270,40.788627052,33.645915656090134178,26,57.363241951015704537,54.593769007141402501,38.281041982557567754,'N9','OGK_345_DF3G','bX',84,'dY','2010-01-01'),
('2017-01-01','c4','1','4z','XYZ','m2','3x',45.641858931001769490,78.434525445976652527,45.900952883254343920,5.513576956457100970,73.709033426570321194,15.259848945,42.373420393218204258,18,3.666100558275775415,25.135890572510077029,59.910840447327477497,'se','AFD_GFFD_234DF','z9',58,'08','2010-01-01');
"""

    sql """
INSERT INTO `t4` VALUES (NULL,'4M','DORIS','td','1','kQ','6G','XM','k3','Jp','02','xA','xj','yK','v3','GR','es','CO','2010-01-01','2010-01-01','9y','Yi','l6','AR','VC','Yp','MK','m7','Kq','da','XYZ','gU','2010-01-01','2010-01-01'),
(NULL,'Pq','DORIS','Ys','1','yR','FV','kA','z9','1e','02','j2','WB','2g','bW','ve','Md','CO','2010-01-01','2010-01-01','xp','pz','QR','ET','BB','qX','U5','tu','zp','UW','CDE','1L','2010-01-01','2010-01-01'),
(NULL,'Iz','DORIS','7G','1','pl','Tb','ga','gJ','7W','02','Lu','SI','pi','P5','IP','DW','CO','2010-01-01','2010-01-01','pe','di','EU','TW','SU','N5','7v','cP','jn','Hq','ABC','Ub','2010-01-01','2010-01-01'),
(NULL,'xz','DORIS','am','1','d2','4N','Us','wt','yN','02','Al','3d','k8','GR','rD','De','CO','2010-01-01','2010-01-01','LG','bM','Cy','lQ','8E','8p','0b','Xz','Ed','c0','PLM','zz','2010-01-01','2010-01-01'),
(NULL,'WP','DORIS','Xm','1','hr','ee','xv','N7','9K','08','fO','NS','sy','DD','wR','O5','CO','2010-01-01','2010-01-01','Xe','bd','pf','J6','wB','rj','0V','fh','oF','zc','XYZ','W8','2010-01-01','2010-01-01'),
(NULL,'Ae','DORIS','5t','1','qW','tZ','2Q','JX','7w','08','qG','GJ','gd','3s','Z3','wT','CO','2010-01-01','2010-01-01','UM','6v','Q3','Vh','AA','3F','AU','4i','Wt','4A','CDE','Cn','2010-01-01','2010-01-01'),
(NULL,'Qd','DORIS','tw','1','S3','Kq','aU','Ks','Oc','08','Dz','AD','s2','ei','g3','qJ','CO','2010-01-01','2010-01-01','63','Aj','7C','Gw','YL','zf','Cq','gs','mT','to','ABC','Qu','2010-01-01','2010-01-01'),
(NULL,'fy','DORIS','sq','1','RE','PO','eV','Cn','2j','08','Ah','Tr','KT','Ho','6q','YD','CO','2010-01-01','2010-01-01','9q','05','KC','1a','Bp','zD','om','mp','zQ','qC','PLM','1c','2010-01-01','2010-01-01');
"""

    sql """
set parallel_fragment_exec_instance_num = 1;
"""
    sql """
set enable_pipeline_engine = 0;
"""
    sql """
set enable_profile = 1;
"""

    test {
        sql """
SELECT DISTINCT
A.c0 e0 
,B.d0 e1 
,B.d2 e2
,B.d14 e3 
,A.c2 d4
,A.c5 d9 
,A.c4 e6
,A.c7 e7 
,A.c8 e8 
,A.c10 e9 
,A.c11 e10 
,A.c9 e11 
,A.c9-A.c7 e12 
,A.c15/100 e13 
,A.c12 e14 
,A.c13 e15 
,NULL e16 
,NULL AS e17
,NULL AS e18
,A.c4 d30 
,NULL d31 
FROM t3 A
INNER JOIN t4 B
ON A.c2=B.d4
AND B.d30='XYZ'
WHERE
A.c19 ='AFD_GFFD_234DF'
AND A.c0 BETWEEN '2000-01-01' AND '2022-01-01'
;
"""
        rowNum 16
    }
}
